# -*- coding: utf-8 -*-            
# @Author : chenliang
# @Time : 2024/10/25 16:31

import pandas as pd
from datetime import datetime
from Common.business_common_tool import BusinessCommonTools
from Common.get_sql_object import GetSqlObject
from Common.tool import Tool
from Common.dingtalk import Ding
from datetime import timedelta
from Common.do_mysql import DoMysql
import os

# 获取当前日期
now = datetime.now()
end_year, end_month = now.year, now.month
# 生成从2024年11月到当前月份的列表
months_list = Tool().generate_months(2024, 11, end_year, end_month)
months_list.reverse() #反转列表

# 获取本周日期列表
data_list = Tool().get_current_week()



mysql_source = GetSqlObject.get_instance("PRO")
#组装成excel数据,放到字典中去
df = {}
for i in months_list:
    query_waybill_list = BusinessCommonTools().get_query_waybill_list2('PRO',i, mysql_source)
    df[i]= pd.DataFrame(query_waybill_list)

# 已审核通过车辆,已审核通过货主,新增加线路物流企业
car_list,car_name,amount_list=BusinessCommonTools().get_car_list('PRO',data_list[0],data_list[-1],mysql_source)
query_consignor_sum,query_consignor_list,query_logistics_line,query_logistics_line_today,query_waybill_end_sum,query_waybill_list_today = BusinessCommonTools().get_consignor_and_cooperation_list('PRO',data_list[0],data_list[-1],mysql_source)
#组装成excel数据
df1 = pd.DataFrame(car_list)
df2 = pd.DataFrame(query_consignor_list)
df3 = pd.DataFrame(query_logistics_line_today)

# 指定要创建的文件夹路径
# 创建文件夹，如果文件夹不存在则创建，如果存在则不创建
os.makedirs(f'File/{months_list[0]}', exist_ok=True)

# 指定要导出的Excel文件名
excel_file = f'/File/{months_list[0]}/11月至今运单详情{now.date()}.xlsx' # 生产环境文件存放地址
#excel_file = f'File/{months_list[0]}/11月至今运单详情{now.date()}.xlsx'
excel_file_path = f'http://47.116.122.206{excel_file}'

# 使用ExcelWriter对象
with pd.ExcelWriter(excel_file, engine='xlsxwriter') as writer:
     for i in months_list:
         df[i].to_excel(writer, sheet_name=i, index=False)
     df1.to_excel(writer, sheet_name=f'车辆{data_list[0]}-{data_list[-1]}', index=False)
     df2.to_excel(writer, sheet_name=f'货主{data_list[0]}-{data_list[-1]}', index=False)
     df3.to_excel(writer, sheet_name=f'线路物流企业{data_list[0]}-{data_list[-1]}', index=False)

result_string = f'统计本周数据{data_list[0]}至{data_list[-1]}\n'
if amount_list:
    a1 = round(float(amount_list[0]["amount"]), 2)  # 收入
    a2 = round(float(amount_list[1]["amount"]), 2)  # 支出
else:
    a1=0
    a2=0
a3=round((a1-a2),2) # 收益

result_string = f"{now.date()}统计:\n"
result_string += f"11月至今运单详情(点击下载)：{excel_file_path}\n"
result_string += f'本周{data_list[0]}至{data_list[-1]}账单\n'
result_string += f'收入:{a1}  支出:{a2}  收益:{a3}\n'
result_string += "1、本周新增加已审核通过车辆"+str(len(car_list))+':\n'
result_string += "2、本周新增加已审核通过货主"+str(len(query_consignor_list))+':\n'
result_string += "3、本周新增加线路物流企业"+str(len(query_logistics_line_today))+':\n'
print(result_string)

Ding().ding_hami(result_string)





